﻿CREATE FUNCTION [dbo].[PurchaseBatch_Disbursement](
	@BatchID int
)RETURNS TABLE
RETURN
SELECT
*,
AcquisitionFee+TotalDeductFee ProcessingFees,
Subtotal TotalDisbursed
FROM(

SELECT
PB.ID AS BatchID,
PB.BatchNumber,
CH.Name AS ContractHolder,
CH.Street AS ContractHolderStreet,
CH.City AS ContractHolderCity,
CH.State AS ContractHolderState,
CH.Zip AS ContractHolderZip,
CH.City +', '+CH.State + ' '+ CH.Zip AS ContractHolderCityStateZip,
CH.ContactName, CH.ContactPhone,
S.StateName AS ContractHolderStateName,
CH.AgreementDate,
PB_Ls.Count AS LoansCountTotal,
PB_Ls_OK.CountOK AS LoansCountOK,
PB_Ls_OK.CountRejected AS LoansCountRejected,

PB_Ls_OK.AmountFinanced AS PrincipleBalance,
PB_Ls_OK.AmountFinanced AS AmountFinanced,
PB_Ls_OK.TrackerReward,
PB_Ls_OK.Reserve AS Reserve,
PB_Ls_OK.CheckToDealer,
PB_Ls_OK.Fee AS AcquisitionFee,
PB_Ls_OK.OverageFee,
L_CC.OtherFee,
PB_Ls_OK.TotalDeductFee,
			
PB_Ls_OK.AmountFinanced -
PB_Ls_OK.Reserve -
PB_Ls_OK.Fee -
PB_Ls_OK.TotalDeductFee Subtotal,

CONVERT(varchar,DatePart(qq,DATEADD(qq,1,StatusDate))) + 'Q '+CONVERT(varchar,DatePart(yy,DATEADD(qq,1,StatusDate))) [Quarter]
FROM 
(SELECT * FROM dbo.v_PurchaseBatch WHERE ID = @BatchID) AS PB
INNER JOIN dbo.v_ContractHolder AS CH ON PB.ContractHolderID = CH.ID
INNER JOIN ts_State S ON CH.State = S.StateCode
CROSS JOIN 
	(SELECT COUNT(*) AS Count, BatchID FROM dbo.t_PurchaseBatch_Loans WHERE BatchID = @BatchID GROUP BY BatchID
) AS PB_Ls
LEFT OUTER JOIN 
	(SELECT SUM(OtherFeeValue) AS OtherFee
	 FROM dbo.t_PurchaseBatch_Loans PBLs
	 INNER JOIN t_Loan_ContractConditions LCC ON PBLs.LoanID = LCC.LoanID
	 WHERE PBLs.BatchID = @BatchID
	 GROUP BY PBLs.BatchID
) AS L_CC ON @BatchID = @BatchID
CROSS JOIN
	(SELECT
	 COUNT(CASE WHEN OK>=0 THEN 1 ELSE NULL END) AS CountOK,
	 COUNT(CASE WHEN OK< 0 THEN 1 ELSE NULL END) AS CountRejected,
	 SUM(CASE WHEN OK>=0 THEN AmountFinanced ELSE 0 END) AS AmountFinanced,
	 SUM(CASE WHEN OK>=0 THEN TrackerReward ELSE 0 END) AS TrackerReward,
	 SUM(CASE WHEN OK>=0 THEN Reserve ELSE 0 END) AS Reserve,
	 SUM(CASE WHEN OK>=0 THEN CheckToDealer ELSE 0 END) AS CheckToDealer,
	 SUM(CASE WHEN OK>=0 THEN CONVERT(numeric(18,2),Fee) ELSE 0.00 END) AS Fee,
	 SUM(CASE WHEN OK>=0 THEN CONVERT(numeric(18,2),OverageFee) ELSE 0.00 END) AS OverageFee,
	 SUM(CASE WHEN OK>=0 THEN CONVERT(numeric(18,2),TotalDeductFee) ELSE 0.00 END) AS TotalDeductFee
	 FROM PurchaseBatch_Loans(@BatchID) 
) AS PB_Ls_OK

)T	



